select * from trb_lancamento where tla_cod = 806255
select * from trb_baixa where tla_cod = 806255

select count(*) from trb_lancamento b where b.tla_cod = a.tla_cod and lan_parcela > 0 and lan_situacao = 'N' and not exists ( select 1 from trb_baixa c where c.lan_cod = b.lan_cod )

select tla_cod
	from trb_iptu a
	where exe_ano = 2007
	and not exists ( select 1 from trb_lancamento b join trb_baixa c on c.lan_cod = b.lan_cod where b.tla_cod = a.tla_cod and lan_parcela = 0 and lan_situacao = 'N' )
	and ( select count(*) from trb_lancamento b where b.tla_cod = a.tla_cod and lan_parcela > 0 and lan_situacao = 'N' and not exists ( select 1 from trb_baixa c where c.lan_cod = b.lan_cod ) ) = 10



declare @d int, @v money
select @d = div_cod, @v = lan_valor_original from TRB_DIVIDA_LANCAMENTO a join trb_lancamento b on b.lan_cod = a.lan_cod where tla_cod = 856994 and b.lan_parcela = 0
select @d, @v


SELECT PLA_SIGLA FROM GER_PLANO_DE_CONTAS_VI WHERE PLA_COD = 4373 -- IPTU
SELECT PLA_SIGLA FROM GER_PLANO_DE_CONTAS_VI WHERE PLA_COD = 4376 -- ISS
SELECT PLA_SIGLA FROM GER_PLANO_DE_CONTAS_VI WHERE PLA_COD = 4566 -- TFF
SELECT PLA_SIGLA FROM GER_PLANO_DE_CONTAS_VI WHERE PLA_COD = 7056 -- TLL

select pla_sigla, * from ger_plano_de_contas_vi where pla_sigla like '%TLL%'

-- IPTU - 10 Parcelas
SELECT 
	  A.DIV_COD
	, DIV_VALOR_ORIGINAL
	, LAN_VALOR_ORIGINAL
	, A.PLA_COD
	FROM TRB_DIVIDA 			A
	JOIN TRB_DIVIDA_LANCAMENTO 	B ON B.DIV_COD = A.DIV_COD
	JOIN TRB_LANCAMENTO 		C ON C.LAN_COD = B.LAN_COD
	WHERE 
	C.LAN_PARCELA = 0
	AND A.PLA_COD = 4373
	AND NOT EXISTS (  SELECT 1 FROM TRB_BAIXA D WHERE D.LAN_COD = C.LAN_COD AND BXA_SITUACAO = 'N' )
	AND ( SELECT COUNT(*) FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA > 0 AND NOT EXISTS ( SELECT 1 FROM TRB_BAIXA E WHERE E.LAN_COD = D.LAN_COD AND BXA_SITUACAO = 'N' ) ) = 10
	AND DIV_VALOR_ORIGINAL != LAN_VALOR_ORIGINAL

-- ISS Autonomo - 5 Parcelas - 4376
SELECT 
	  A.DIV_COD
	, DIV_VALOR_ORIGINAL
	, LAN_VALOR_ORIGINAL
	, A.PLA_COD
	, LAN_PARCELA
	FROM TRB_DIVIDA 			A
	JOIN TRB_DIVIDA_LANCAMENTO 	B ON B.DIV_COD = A.DIV_COD
	JOIN TRB_LANCAMENTO 		C ON C.LAN_COD = B.LAN_COD
	WHERE 
	C.LAN_PARCELA = 0
	AND A.PLA_COD = 4376
	AND NOT EXISTS (  SELECT 1 FROM TRB_BAIXA D WHERE D.LAN_COD = C.LAN_COD AND BXA_SITUACAO = 'N' )
	AND ( SELECT COUNT(*) FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA > 0 AND NOT EXISTS ( SELECT 1 FROM TRB_BAIXA E WHERE E.LAN_COD = D.LAN_COD AND BXA_SITUACAO = 'N' ) ) = 5
	AND DIV_VALOR_ORIGINAL != LAN_VALOR_ORIGINAL

-- TFF - 6 Parcelas - 4566
SELECT 
	  A.DIV_COD, EXE_ANO
	, C.TLA_COD, B.LAN_COD
	, DIV_VALOR_ORIGINAL
	, LAN_VALOR_ORIGINAL
	, A.PLA_COD
	, LAN_PARCELA
	FROM TRB_DIVIDA 			A
	JOIN TRB_DIVIDA_LANCAMENTO 	B ON B.DIV_COD = A.DIV_COD
	JOIN TRB_LANCAMENTO 		C ON C.LAN_COD = B.LAN_COD
	WHERE 
	C.LAN_PARCELA = 0
	AND A.PLA_COD = 4566
	AND NOT EXISTS (  SELECT 1 FROM TRB_BAIXA D WHERE D.LAN_COD = C.LAN_COD AND BXA_SITUACAO = 'N' )
	AND ( SELECT COUNT(*) FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA > 0 AND NOT EXISTS ( SELECT 1 FROM TRB_BAIXA E WHERE E.LAN_COD = D.LAN_COD AND BXA_SITUACAO = 'N' ) ) = 6
	AND DIV_VALOR_ORIGINAL != LAN_VALOR_ORIGINAL
	AND NOT LAN_VALOR_ORIGINAL BETWEEN  (SELECT LAN_VALOR_ORIGINAL FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA = 1) - 1 
								AND (SELECT LAN_VALOR_ORIGINAL FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA = 1) + 1

-- TLL - 6 Parcelas - 7056
SELECT 
	  A.DIV_COD, EXE_ANO
	, C.TLA_COD, B.LAN_COD
	, DIV_VALOR_ORIGINAL
	, LAN_VALOR_ORIGINAL
	, A.PLA_COD
	, LAN_PARCELA
	FROM TRB_DIVIDA 			A
	JOIN TRB_DIVIDA_LANCAMENTO 	B ON B.DIV_COD = A.DIV_COD
	JOIN TRB_LANCAMENTO 		C ON C.LAN_COD = B.LAN_COD
	WHERE 
	C.LAN_PARCELA = 0
	AND A.PLA_COD = 7056
	AND NOT EXISTS (  SELECT 1 FROM TRB_BAIXA D WHERE D.LAN_COD = C.LAN_COD AND BXA_SITUACAO = 'N' )
	AND ( SELECT COUNT(*) FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA > 0 AND NOT EXISTS ( SELECT 1 FROM TRB_BAIXA E WHERE E.LAN_COD = D.LAN_COD AND BXA_SITUACAO = 'N' ) ) = 6
	AND DIV_VALOR_ORIGINAL != LAN_VALOR_ORIGINAL		


-- UPDATES

-- TFF - 6 Parcelas - 4566
BEGIN TRANSACTION
	UPDATE TRB_DIVIDA SET DIV_VALOR_ORIGINAL = LAN_VALOR_ORIGINAL
		FROM TRB_DIVIDA 			A
		JOIN TRB_DIVIDA_LANCAMENTO 	B ON B.DIV_COD = A.DIV_COD
		JOIN TRB_LANCAMENTO 		C ON C.LAN_COD = B.LAN_COD
		WHERE 
		C.LAN_PARCELA = 0
		AND A.PLA_COD = 4566
		AND NOT EXISTS (  SELECT 1 FROM TRB_BAIXA D WHERE D.LAN_COD = C.LAN_COD AND BXA_SITUACAO = 'N' )
		AND ( SELECT COUNT(*) FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA > 0 AND NOT EXISTS ( SELECT 1 FROM TRB_BAIXA E WHERE E.LAN_COD = D.LAN_COD AND BXA_SITUACAO = 'N' ) ) = 6
		AND DIV_VALOR_ORIGINAL != LAN_VALOR_ORIGINAL
		AND NOT LAN_VALOR_ORIGINAL BETWEEN  
				(SELECT LAN_VALOR_ORIGINAL FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA = 1) - 1 AND 
				(SELECT LAN_VALOR_ORIGINAL FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA = 1) + 1
COMMIT TRANSACTION

-- ISS Autonomo - 5 Parcelas - 4376
BEGIN TRANSACTION
	UPDATE TRB_DIVIDA SET DIV_VALOR_ORIGINAL = LAN_VALOR_ORIGINAL
		FROM TRB_DIVIDA 			A
		JOIN TRB_DIVIDA_LANCAMENTO 	B ON B.DIV_COD = A.DIV_COD
		JOIN TRB_LANCAMENTO 		C ON C.LAN_COD = B.LAN_COD
		WHERE 
		C.LAN_PARCELA = 0
		AND A.PLA_COD = 4376
		AND NOT EXISTS (  SELECT 1 FROM TRB_BAIXA D WHERE D.LAN_COD = C.LAN_COD AND BXA_SITUACAO = 'N' )
		AND ( SELECT COUNT(*) FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA > 0 AND NOT EXISTS ( SELECT 1 FROM TRB_BAIXA E WHERE E.LAN_COD = D.LAN_COD AND BXA_SITUACAO = 'N' ) ) = 5
		AND DIV_VALOR_ORIGINAL != LAN_VALOR_ORIGINAL
COMMIT TRANSACTION

-- IPTU - 10 Parcelas
BEGIN TRANSACTION
	UPDATE TRB_DIVIDA SET DIV_VALOR_ORIGINAL = LAN_VALOR_ORIGINAL
	FROM TRB_DIVIDA 			A
	JOIN TRB_DIVIDA_LANCAMENTO 	B ON B.DIV_COD = A.DIV_COD
	JOIN TRB_LANCAMENTO 		C ON C.LAN_COD = B.LAN_COD
	WHERE 
	C.LAN_PARCELA = 0
	AND A.PLA_COD = 4373
	AND NOT EXISTS (  SELECT 1 FROM TRB_BAIXA D WHERE D.LAN_COD = C.LAN_COD AND BXA_SITUACAO = 'N' )
	AND ( SELECT COUNT(*) FROM TRB_LANCAMENTO D WHERE D.TLA_COD = C.TLA_COD AND LAN_PARCELA > 0 AND NOT EXISTS ( SELECT 1 FROM TRB_BAIXA E WHERE E.LAN_COD = D.LAN_COD AND BXA_SITUACAO = 'N' ) ) = 10
	AND DIV_VALOR_ORIGINAL != LAN_VALOR_ORIGINAL
COMMIT TRANSACTION
